package com.jfinal.ext.kit;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.*;

import com.jfinal.plugin.activerecord.Db;
import org.apache.poi.ss.usermodel.CellStyle;

public class PoiExt {
    public static Map<String, Object> ReadExcel(String filename,String sheetName, String sql, int startRow,int startCol,int ColCount) {
        Map<String, Object> map = null;
        try {
            @SuppressWarnings("resource")
            HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filename));
            HSSFSheet sheet = wookbook.getSheet(sheetName);
            int rows = sheet.getPhysicalNumberOfRows();
            Object[][] paras = new Object[rows-startRow][ColCount];
            for (int i = 0; i < rows-startRow; i++) {
                HSSFRow row = sheet.getRow(i+startRow);
                if (row != null) {
                    int cells = row.getPhysicalNumberOfCells();
                    for (int j =0; j < ColCount; j++) {
                        HSSFCell cell = row.getCell(j+startCol-1);
                        if (cell != null) {
                            switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_FORMULA:
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                String result = new String();
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                                    SimpleDateFormat sdf = null;
                                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
                                            .getBuiltinFormat("h:mm")) {
                                        sdf = new SimpleDateFormat("HH:mm");
                                    } else {// 日期
                                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                                    }
                                    Date date = cell.getDateCellValue();
                                    result = sdf.format(date);
                                } else if (cell.getCellStyle().getDataFormat() == 58) {
                                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                    double value = cell.getNumericCellValue();
                                    Date date = org.apache.poi.ss.usermodel.DateUtil
                                            .getJavaDate(value);
                                    result = sdf.format(date);
                                } else {

                                    double value = cell.getNumericCellValue();
                                    CellStyle style = cell.getCellStyle();
                                    DecimalFormat format = new DecimalFormat();
                                    String temp = style.getDataFormatString();
                                     //单元格设置成常规
                                    if (temp.equals("General")) {
                                        format.applyPattern("#.######");
                                    }
                                    result = format.format(value);

                                }
                                paras[i][j] =result;
                                break;
                            case HSSFCell.CELL_TYPE_STRING:
                                paras[i][j] = cell.getStringCellValue();
                                break;
                            default:
                                paras[i][j] = null;
                                break;
                            }
                        }
                    }
                }
            }
            int[] ret = Db.batch(sql, paras,100);
            int s = 0, l = 0;
            for (int i = 0; i < ret.length; i++)
                if (ret[i] == 1)
                    s++;
                else
                    l++;
            map = new HashMap<String, Object>();
            map.put("success", s);
            map.put("lost", l);
            map.put("count", ret.length);
            wookbook = null;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return map;
    }
}